package db.tables.reference;

import java.sql.SQLException;
import java.util.ArrayList;

import org.hsqldb.Types;

import db.DBObject;
import db.tables.DBCategoryTable;
import db.tables.DBUserTable;
import db.tables.DBWorkTable;

public class DBWorkAtTable extends DBObject {

	public static final String TABLE_NAME="work_at";
	
	public static final String WORK_AT_ID_COL = TABLE_NAME+"_id",
			WORK_ID_COL = DBWorkTable.WORK_ID_COL,
			USER_ID_COL = DBUserTable.USER_ID_COL,
			CATEGORY_ID_COL = DBCategoryTable.CATEGORY_ID_COL, //Represents the position at work
			ADD_INFO_COL = "additional_info",
			START_DATE_COL = "start_date",
			END_DATE_COL = "end_date";

	public DBWorkAtTable() throws ClassNotFoundException, SQLException {
		super(TABLE_NAME, new Object[][] {
				{ WORK_AT_ID_COL, Types.BIGINT, SQL_IDENTITY_PRIMARY_KEY, "" },
				{ WORK_ID_COL, Types.BIGINT, SQL_NOT_NULL, "" },
				{ USER_ID_COL, Types.BIGINT, SQL_NOT_NULL, "" },
				{ CATEGORY_ID_COL, Types.BIGINT, "", "" },
				{ ADD_INFO_COL, Types.VARCHAR, "", ""},
				{ START_DATE_COL, Types.DATE, SQL_NOT_NULL, getSQLCheckStartDate() },
				{ END_DATE_COL, Types.DATE, getSQLCheckEndDate()
					+getSQLForeignKeyReference(DBWorkTable.TABLE_NAME,WORK_ID_COL)
					+getSQLForeignKeyReference(DBUserTable.TABLE_NAME,USER_ID_COL)
					+getSQLForeignKeyReference(DBCategoryTable.TABLE_NAME,CATEGORY_ID_COL),"" }});
	}
	
	public ArrayList<ArrayList<Object>> getArrayListByUserID(Long user_id){
		return getReferenceTableJoin(DBWorkAtTable.TABLE_NAME, DBWorkTable.TABLE_NAME, 
				getAbsoluteColumn(WORK_ID_COL), 
				getAbsoluteColumn(DBWorkTable.TABLE_NAME, DBWorkTable.WORK_ID_COL), 
				getAbsoluteColumn(USER_ID_COL),user_id);
	}
}
